﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Test.DAL
{
    class SqlHelper
    {
        private static string strConn = ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString;
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="sqlParameters">参数列表</param>
        /// <returns></returns>
        public static DataTable Query(string sql, params SqlParameter[] sqlParameters) //params 多个参数
        {
            DataTable table = new DataTable();
            SqlDataAdapter data = new SqlDataAdapter(sql, strConn);
            if (sqlParameters.Length > 0)//判断是否为空
            {
                //可变数组参数添加
                data.SelectCommand.Parameters.AddRange(sqlParameters);
            }
            data.Fill(table);
            return table;
        }
        /// <summary>
        /// 单条查询
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="sqlParameters">参数列表</param>
        /// <returns></returns>
        public static SqlDataReader Reader(string sql, params SqlParameter[] sqlParameters) //params 多个参数
        {
            SqlConnection sqlConnection = new SqlConnection(strConn);
            sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
            //运行失败要声明标量变量@id原因：没有判断if
            if (sqlParameters.Length > 0)
                //将数据存储
                sqlCommand.Parameters.AddRange(sqlParameters);

            //SqlDataReader dataReader = sqlCommand.ExecuteReader();
            //CommandBehavior.CloseConnection:返回对象前不关闭数据库连接
            SqlDataReader dataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
            return dataReader;
        }
        /// <summary>
        /// 非查询
        /// </summary>
        /// <param name="sql">非查询语句</param>
        /// <param name="sqlParameters">参数列表</param>
        /// <returns></returns>
        public static int NonQuery(string sql,CommandType type=CommandType.Text, params SqlParameter[] sqlParameters) //params 多个参数
        {
            int num = 0;
            using (SqlConnection sqlConnection = new SqlConnection(strConn))
            {
                using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection))
                {
                    sqlCommand.CommandType = type;
            try
            {
                sqlConnection.Open();
                        if (sqlParameters.Length > 0)
                            //将数据存储
                            sqlCommand.Parameters.AddRange(sqlParameters);
                        num = sqlCommand.ExecuteNonQuery();
            }
            finally
            {
                if (sqlConnection.State == ConnectionState.Open)
                    sqlConnection.Close();
            }

            return num;
                }

            }
        }
        

    }
}
